<?php

namespace app\adminapi\service;

use think\Db;
use think\Exception;

/**
 * 批量更新函数
 */
class BatchSqlServer
{
    //批量更新函数
    /**
     * 批量更新函数
     * @param        $data   array 待更新的数据，二维数组格式
     * @param array  $params array 值相同的条件，键值对应的一维数组
     * @param string $field  string 值不同的条件，默认为id
     * @return bool|string
     */
    public static function batchUpdate($table, $data, $field = 'id', $params = [])
    {
        if(!is_array($data) || !$field || !is_array($params) || !$table) {
            return false;
        }
        $table_name = config('database.prefix') . $table;
        if(count($data) > 3000) {
            $final_data = array_chunk($data, 3000);
            try {
                $res = 1;
                foreach($final_data as $item) {
                    $updates = self::parseUpdate($item, $field);
                    $where = self::parseParams($params);
                    $fields = array_column($item, $field);
                    $fields = implode(',', array_map(function($value) {
                        return "'" . $value . "'";
                    }, $fields));
                    $sql = sprintf("UPDATE `%s` SET %s WHERE `%s` IN (%s) %s", $table_name, $updates, $field, $fields, $where);
                    $res = Db::execute($sql);

                }
                return $res;
            } catch(Exception $e) {
                throw new Exception($e->getMessage());
            }
        } else {
            try {
                $updates = self::parseUpdate($data, $field);
                $where = self::parseParams($params);
                $fields = array_column($data, $field);
                $fields = implode(',', array_map(function($value) {
                    return "'" . $value . "'";
                }, $fields));
                $sql = sprintf("UPDATE `%s` SET %s WHERE `%s` IN (%s) %s", $table_name, $updates, $field, $fields, $where);
                $res = Db::execute($sql);
                return $res;
            } catch(Exception $e) {
                throw new Exception($e->getMessage());
            }
        }
    }

    /**
     * 将二维数组转换成CASE WHEN THEN的批量更新条件
     * @param $data  array 二维数组
     * @param $field string 列名
     * @return string sql语句
     */
    public static function parseUpdate($data, $field)
    {
        $sql = '';
        $keys = array_keys(current($data));
        foreach($keys as $column) {
            $sql .= sprintf("`%s` = CASE `%s` \n", $column, $field);
            foreach($data as $line) {
                $sql .= sprintf("WHEN '%s' THEN '%s' \n", $line[$field], str_replace(["'", '`', '"'], '', $line[$column]));
            }
            $sql .= "END,";
        }
        return rtrim($sql, ',');
    }

    /**
     * 解析where条件
     * @param $params
     * @return array|string
     */
    public static function parseParams($params)
    {
        $where = [];
        foreach($params as $key => $value) {
            $where[] = sprintf("`%s` = '%s'", $key, $value);
        }
        return $where ? ' AND ' . implode(' AND ', $where) : '';
    }
}